/**
 * 装备基础数据数据库更新脚本
 * 功能：根据装备基础数据.json文件更新远程数据库equipment_base表格
 * 字段：id、lvl、price、type（状态类型分类）、base（装备细分类）
 */

const mysql = require('mysql2/promise');
const fs = require('fs');
const path = require('path');

// 数据库连接配置
const DB_CONFIG = {
    host: '43.143.253.188',
    port: 3306,
    user: 'nextjs',
    password: 'yEAiRE2jcxTyRBy5',
    database: 'nextjs',
    charset: 'utf8mb4',
    connectionLimit: 10
};

class EquipmentBaseUpdater {
    constructor() {
        this.connection = null;
        this.equipmentData = null;
    }

    // 连接数据库
    async connectDatabase() {
        try {
            this.connection = await mysql.createConnection(DB_CONFIG);
            console.log('✅ 数据库连接成功');
        } catch (error) {
            console.error('❌ 数据库连接失败:', error.message);
            throw error;
        }
    }

    // 关闭数据库连接
    async closeDatabase() {
        if (this.connection) {
            await this.connection.end();
            console.log('🔌 数据库连接已关闭');
        }
    }

    // 读取装备基础数据JSON文件
    loadEquipmentData() {
        try {
            const filePath = path.join(__dirname, 'output', '装备基础数据.json');
            const fileContent = fs.readFileSync(filePath, 'utf8');
            this.equipmentData = JSON.parse(fileContent);
            console.log(`📁 成功读取装备基础数据文件: ${filePath}`);
            console.log(`📊 数据条目数: ${Object.keys(this.equipmentData).length}`);
        } catch (error) {
            console.error('❌ 读取装备基础数据文件失败:', error.message);
            throw error;
        }
    }

    // 检查equipment_base表是否存在，并添加缺失的字段
    async ensureTableExists() {
        try {
            // 检查表是否存在
            const [tables] = await this.connection.execute(`
                SELECT TABLE_NAME 
                FROM INFORMATION_SCHEMA.TABLES 
                WHERE TABLE_SCHEMA = 'nextjs' AND TABLE_NAME = 'equipment_base'
            `);

            if (tables.length === 0) {
                // 如果表不存在，创建新表
                const createTableSQL = `
                    CREATE TABLE equipment_base (
                        id INT PRIMARY KEY,
                        name VARCHAR(255) NULL COMMENT '装备名称',
                        image VARCHAR(255) NULL COMMENT '装备图片',
                        level INT NULL COMMENT '装备等级',
                        weight INT NULL COMMENT '装备负重',
                        price VARCHAR(255) NULL COMMENT '装备价格',
                        intro TEXT NULL COMMENT '装备介绍',
                        req_level INT NULL COMMENT '需要等级',
                        req_strength INT NULL COMMENT '需要力量',
                        req_constitution INT NULL COMMENT '需要体质',
                        req_agility INT NULL COMMENT '需要敏捷',
                        req_intelligence INT NULL COMMENT '需要智力',
                        bonus_attack INT NULL COMMENT '攻击力加成',
                        bonus_hp INT NULL COMMENT '气血加成',
                        bonus_mp INT NULL COMMENT '精力加成',
                        bonus_defense INT NULL COMMENT '防御力加成',
                        resistance_type VARCHAR(255) NULL COMMENT '随机抗性类型',
                        resistance_value INT NULL COMMENT '抗性值',
                        raw_json JSON NULL COMMENT '原始数据',
                        type INT NULL COMMENT '状态类型分类',
                        base INT NULL COMMENT '装备细分类',
                        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                        INDEX idx_type_base (type, base),
                        INDEX idx_level (level),
                        INDEX idx_price (price)
                    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
                `;
                await this.connection.execute(createTableSQL);
                console.log('✅ equipment_base表创建完成');
            } else {
                // 表已存在，检查并添加缺失的字段
                await this.addMissingColumns();
                console.log('✅ equipment_base表字段检查完成');
            }
        } catch (error) {
            console.error('❌ 处理equipment_base表失败:', error.message);
            throw error;
        }
    }

    // 添加缺失的字段
    async addMissingColumns() {
        try {
            // 检查并添加 type 字段
            const [typeColumn] = await this.connection.execute(`
                SELECT COLUMN_NAME 
                FROM INFORMATION_SCHEMA.COLUMNS 
                WHERE TABLE_SCHEMA = 'nextjs' AND TABLE_NAME = 'equipment_base' AND COLUMN_NAME = 'type'
            `);

            if (typeColumn.length === 0) {
                await this.connection.execute(`
                    ALTER TABLE equipment_base 
                    ADD COLUMN type INT NULL COMMENT '状态类型分类' AFTER resistance_value
                `);
                console.log('✅ 添加 type 字段');
            }

            // 检查并添加 base 字段
            const [baseColumn] = await this.connection.execute(`
                SELECT COLUMN_NAME 
                FROM INFORMATION_SCHEMA.COLUMNS 
                WHERE TABLE_SCHEMA = 'nextjs' AND TABLE_NAME = 'equipment_base' AND COLUMN_NAME = 'base'
            `);

            if (baseColumn.length === 0) {
                await this.connection.execute(`
                    ALTER TABLE equipment_base 
                    ADD COLUMN base INT NULL COMMENT '装备细分类' AFTER type
                `);
                console.log('✅ 添加 base 字段');
            }

            // 添加索引
            try {
                await this.connection.execute(`
                    CREATE INDEX IF NOT EXISTS idx_type_base ON equipment_base (type, base)
                `);
                console.log('✅ 添加 type_base 索引');
            } catch (error) {
                // 索引可能已存在，忽略错误
            }

        } catch (error) {
            console.error('❌ 添加字段失败:', error.message);
            throw error;
        }
    }

    // 清空现有数据（可选）
    async clearExistingData() {
        try {
            await this.connection.execute('DELETE FROM equipment_base');
            console.log('🗑️ 已清空equipment_base表现有数据');
        } catch (error) {
            console.error('❌ 清空数据失败:', error.message);
            throw error;
        }
    }

    // 批量插入装备数据
    async insertEquipmentData() {
        try {
            const insertSQL = `
                INSERT INTO equipment_base (id, level, name, price, type, base, raw_json) 
                VALUES (?, ?, ?, ?, ?, ?, ?)
                ON DUPLICATE KEY UPDATE
                    level = VALUES(level),
                    name = VALUES(name),
                    price = VALUES(price),
                    type = VALUES(type),
                    base = VALUES(base),
                    raw_json = VALUES(raw_json),
                    updated_at = CURRENT_TIMESTAMP
            `;

            let totalInserted = 0;
            let totalUpdated = 0;

            // 遍历所有装备数据
            for (const [key, categoryData] of Object.entries(this.equipmentData)) {
                const { type, base, data } = categoryData;

                console.log(`📝 处理 type=${type}, base=${base}, 装备数量=${data.length}`);

                for (const equipment of data) {
                    try {
                        const [result] = await this.connection.execute(insertSQL, [
                            equipment.id,
                            equipment.lvl,
                            equipment.name,
                            equipment.price.toString(),
                            type,
                            base,
                            JSON.stringify(equipment)
                        ]);

                        if (result.affectedRows === 1) {
                            totalInserted++;
                        } else if (result.affectedRows === 2) {
                            totalUpdated++;
                        }
                    } catch (error) {
                        console.error(`❌ 插入装备数据失败 ID=${equipment.id}:`, error.message);
                    }
                }
            }

            console.log(`✅ 数据插入完成 - 新增: ${totalInserted}, 更新: ${totalUpdated}`);
            return { inserted: totalInserted, updated: totalUpdated };
        } catch (error) {
            console.error('❌ 批量插入装备数据失败:', error.message);
            throw error;
        }
    }

    // 验证数据完整性
    async validateData() {
        try {
            // 统计总数据量
            const [countResult] = await this.connection.execute('SELECT COUNT(*) as total FROM equipment_base');
            const totalCount = countResult[0].total;

            // 按type分组统计
            const [typeStats] = await this.connection.execute(`
                SELECT type, COUNT(*) as count 
                FROM equipment_base 
                GROUP BY type 
                ORDER BY type
            `);

            // 按base分组统计
            const [baseStats] = await this.connection.execute(`
                SELECT base, COUNT(*) as count 
                FROM equipment_base 
                GROUP BY base 
                ORDER BY base
            `);

            console.log('\n📊 数据验证结果:');
            console.log(`   总装备数量: ${totalCount}`);
            console.log('   按type分组统计:');
            typeStats.forEach(stat => {
                console.log(`     type=${stat.type}: ${stat.count} 件装备`);
            });
            console.log('   按base分组统计:');
            baseStats.forEach(stat => {
                console.log(`     base=${stat.base}: ${stat.count} 件装备`);
            });

            return {
                totalCount,
                typeStats,
                baseStats
            };
        } catch (error) {
            console.error('❌ 数据验证失败:', error.message);
            throw error;
        }
    }

    // 执行完整的更新流程
    async runUpdate() {
        console.log('🚀 开始装备基础数据数据库更新...\n');

        try {
            // 1. 连接数据库
            await this.connectDatabase();

            // 2. 读取装备数据
            this.loadEquipmentData();

            // 3. 确保表存在
            await this.ensureTableExists();

            // 4. 清空现有数据（可选，根据需要启用）
            // await this.clearExistingData();

            // 5. 插入/更新装备数据
            const result = await this.insertEquipmentData();

            // 6. 验证数据完整性
            await this.validateData();

            console.log('\n🎉 装备基础数据数据库更新完成！');
            console.log(`   新增记录: ${result.inserted}`);
            console.log(`   更新记录: ${result.updated}`);

        } catch (error) {
            console.error('❌ 更新过程中发生错误:', error.message);
            throw error;
        } finally {
            // 7. 关闭数据库连接
            await this.closeDatabase();
        }
    }
}

// 运行更新
async function main() {
    console.log('🎯 装备基础数据数据库更新模式');
    console.log('   功能：将装备基础数据.json文件同步到远程数据库equipment_base表\n');

    const updater = new EquipmentBaseUpdater();
    await updater.runUpdate();
}

// 如果直接运行此脚本
if (require.main === module) {
    main().catch((error) => {
        console.error('💥 更新脚本执行失败:', error.message);
        process.exit(1);
    });
}

module.exports = EquipmentBaseUpdater;
